package com.sr.data;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sr.tool.SqlHelper;

public class moreDataService {
	//查询项目基础信息以及详细信息是否录入
	public srProject CheckProject(srProject srp)
	{
		String sql="select * from more_info where projectName like ?";
		String parameters[]={"%"+srp.getprojectName()+"%"};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			
			if(rs.next()){
			
				srp.setprojectName(rs.getString(1));
				srp.setContract_Pic(rs.getString(2));
				srp.setTotalMoney(rs.getDouble(3));
				srp.setRealEndDate(rs.getDate(4));
				srp.setCompletionTask(rs.getString(5));
				srp.setPassAudit(rs.getString(6));
				srp.setConclusionReport(rs.getString(7));
				srp.setRank(rs.getString(8));
				srp.setRankContent(rs.getString(9));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}

		return srp;
	}
	
	//得到所有项目更多信息
	public ArrayList<srProject> GetMoreInfo()
	{
		ArrayList<srProject> al=new ArrayList<srProject>();
		String sql="select * from more_info";
		String parameters[]={};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			while(rs.next())
			{
				srProject srp=new srProject();
				srp.setprojectName(rs.getString(1));
				srp.setContract_Pic(rs.getString(2));
				srp.setTotalMoney(rs.getDouble(3));
				srp.setRealEndDate(rs.getDate(4));
				srp.setCompletionTask(rs.getString(5));
				srp.setPassAudit(rs.getString(6));
				srp.setConclusionReport(rs.getString(7));
				srp.setRank(rs.getString(8));
				srp.setRankContent(rs.getString(9));
				al.add(srp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
		}
			
		return al;
	}

	
	//根据项目名查询
	public ArrayList<srProject> getProjectByName(String Name)
	{
		ArrayList<srProject> al=new ArrayList<srProject>();
		String sql="select * from more_info where projectName like ?";
		String parameters[]={"%"+Name+"%"};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			if(rs.next())
			{
				srProject srp=new srProject();
				srp.setprojectName(rs.getString(1));
				srp.setContract_Pic(rs.getString(2));
				srp.setTotalMoney(rs.getDouble(3));
				srp.setRealEndDate(rs.getDate(4));
				srp.setCompletionTask(rs.getString(5));
				srp.setPassAudit(rs.getString(6));
				srp.setConclusionReport(rs.getString(7));
				srp.setRank(rs.getString(8));
				srp.setRankContent(rs.getString(9));
				al.add(srp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}
		return al;
	}
	
	public srProject getProjectById(String Name)
	{
//		ArrayList<srProject> al=new ArrayList<srProject>();
		srProject srp=new srProject();
		String sql="select * from more_info where projectName =?";
		String parameters[]={Name};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			if(rs.next())
			{
				srp.setprojectName(rs.getString(1));
				srp.setContract_Pic(rs.getString(2));
				srp.setTotalMoney(rs.getDouble(3));
				srp.setRealEndDate(rs.getDate(4));
				srp.setCompletionTask(rs.getString(5));
				srp.setPassAudit(rs.getString(6));
				srp.setConclusionReport(rs.getString(7));
				srp.setRank(rs.getString(8));
				srp.setRankContent(rs.getString(9));
//				al.add(srp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}
		return srp;
	}
	
	//根据项目名查询项目数
	public int GetCountOfProjectName(String projectName)
	{		
		int rowCount=0;
		String sql="select COUNT(*) from more_info where projectName like ?";
		String parameters[]={"%"+projectName+"%"};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			while(rs.next())
			{
				rowCount=rs.getInt(1);
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}

		return rowCount;
	}
		
	
	//修改表的内容
	public boolean UpdateProject(srProject srp)
	{
		boolean b=true;
		String sql="update more_info set Contract_Pic=?,TotalMoney=?,RealEndDate=?,CompletionTask=?,PassAudit=?,ConclusionReport=?,Rank=?,RankContent=? where projectName=?";
		String parameters[]={ srp.getContract_Pic(),srp.totalMoney()+"",srp.getDateS3(),
				srp.getCompletionTask(),srp.getPassAudit(),srp.getConclusionReport(),srp.getRank(),
				srp.getRankContent(),srp.getprojectName()+""};
		try {			
			SqlHelper.executeUpdate(sql, parameters);			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			b=false;
		}	
		return b;
	}
	
	//删除
	public  boolean DelProject(String projectName)
	{
		boolean b=true;
		String sql="delete from more_info where projectName=?";
		String parameters[]={projectName};
		try {
			SqlHelper.executeUpdate(sql, parameters);
		} catch (Exception e) {
			e.printStackTrace();
			b=false;
		}
		return b;
	}
	
	//添加记录
	public boolean AddProject(srProject srp)
	{
		boolean b=true;
		String sql="insert into more_info(projectName,Contract_Pic,TotalMoney,RealEndDate,CompletionTask,PassAudit,ConclusionReport,Rank,RankContent) values(?,?,?,?,?,?,?,?,?)";//
		String parameters[]={srp.getprojectName(),srp.getContract_Pic(),srp.totalMoney()+"",srp.getDateS3(),
				srp.getCompletionTask(),srp.getPassAudit(),srp.getConclusionReport(),srp.getRank(),
				srp.getRankContent()};
		try {
			
			SqlHelper.executeUpdate(sql, parameters);
			
		} catch (Exception e) {			
			e.printStackTrace();
			b=false;
		}	
		return b;
	}

}
